package com.sf.dlb.jpa.repository;

import com.sf.dlb.jpa.model.SmsInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * 短信平台 Repository
 *
 * Created by jing.tan on 2018/12/10.
 */
@Repository
public interface SmsInfoRepository extends JpaRepository<SmsInfo, Long> {
	/**
	 * 根据手机号，验证码查询
	 * @param mobile
	 * @param smsValidationCode
	 * @param smsMessageStatus
	 * @param delFlag
	 * @return
	 */
	@Query(value = "SELECT " +
			"* " +
			"FROM sms_info a " +
			"WHERE " +
			"a.del_flag = ?4 " +
			"AND a.sms_message_status=?3 " +
			"AND a.sms_validation_code=?2 " +
			"AND a.mobile =?1 " +
			"order by a.create_date DESC " +
			"LIMIT 1 ",nativeQuery = true)
	SmsInfo findByMobileAndSmsValidationCodeAndSmsMessageStatusAndDelFlag(@Param("mobile") String mobile, @Param("smsValidationCode") String smsValidationCode, @Param("smsMessageStatus") String smsMessageStatus, @Param("delFlag") String delFlag);
	/**
	 * 根据手机号，验证码查询
	 * @param mobile
	 * @param smsValidationCode
	 * @param smsMessageStatus
	 * @param delFlag
	 * @param useState
	 * @return
	 */
	@Query(value = "SELECT " +
			"* " +
			"FROM sms_info a " +
			"WHERE " +
			"a.use_state =?5 " +
			"AND a.del_flag = ?4 " +
			"AND a.sms_message_status=?3 " +
			"AND a.sms_validation_code=?2 " +
			"AND a.mobile =?1 " +
			"order by a.create_date DESC " +
			"LIMIT 1 ",nativeQuery = true)
	SmsInfo findByMobileAndSmsValidationCodeAndSmsMessageStatusAndDelFlag(@Param("mobile") String mobile, @Param("smsValidationCode") String smsValidationCode, @Param("smsMessageStatus") String smsMessageStatus, @Param("delFlag") String delFlag, @Param("useState") String useState);
	/**
     * 根据手机号
     * @param mobile
     * @param delFlag
     * @return
     */
	List<SmsInfo> findByMobileAndDelFlag(String mobile, String delFlag);
}